package com.mcppro.aidatabase.util;

import com.mcppro.aidatabase.dto.IntentDto;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * 安全的SQL生成器
 * 使用参数化查询避免SQL注入
 * 
 * @author MCP Pro
 */
@Component
@Slf4j
public class SafeSqlGenerator {

    // 允许的表名（白名单）
    private static final Set<String> ALLOWED_TABLES = Set.of("users", "chat_logs");
    
    // 允许的字段名（白名单）
    private static final Map<String, Set<String>> ALLOWED_FIELDS = Map.of(
        "users", Set.of("id", "username", "email", "full_name", "age", "status", "created_at", "updated_at"),
        "chat_logs", Set.of("id", "session_id", "user_question", "ai_intent", "generated_sql", 
                           "sql_result", "ai_response", "execution_status", "error_message", 
                           "execution_time_ms", "created_at")
    );

    /**
     * 生成SELECT查询
     */
    public SqlWithParams generateSelect(IntentDto.IntentResult intent) {
        validateTableName(intent.getTableName());
        
        StringBuilder sql = new StringBuilder("SELECT ");
        List<Object> params = new ArrayList<>();
        
        // 处理字段
        if (intent.getFields() == null || intent.getFields().isEmpty() || 
            (intent.getFields().size() == 1 && "*".equals(intent.getFields().get(0)))) {
            sql.append("*");
        } else {
            List<String> validFields = validateAndFilterFields(intent.getTableName(), intent.getFields());
            if (validFields.isEmpty()) {
                // 如果没有有效字段，默认使用*
                sql.append("*");
            } else {
                sql.append(String.join(", ", validFields));
            }
        }
        
        sql.append(" FROM ").append(intent.getTableName());
        
        // 处理WHERE条件
        if (intent.getConditions() != null && !intent.getConditions().isEmpty()) {
            sql.append(" WHERE ");
            appendConditions(sql, params, intent.getConditions(), intent.getTableName());
        }
        
        // 处理GROUP BY
        if (intent.getGroupBy() != null && !intent.getGroupBy().isEmpty()) {
            List<String> validGroupFields = validateAndFilterFields(intent.getTableName(), intent.getGroupBy());
            if (!validGroupFields.isEmpty()) {
                sql.append(" GROUP BY ").append(String.join(", ", validGroupFields));
            }
        }
        
        // 处理ORDER BY
        if (intent.getOrderBy() != null && !intent.getOrderBy().isEmpty()) {
            sql.append(" ORDER BY ");
            List<String> orderClauses = new ArrayList<>();
            for (IntentDto.OrderBy orderBy : intent.getOrderBy()) {
                if (isValidField(intent.getTableName(), orderBy.getField())) {
                    String direction = orderBy.getDirection() == IntentDto.SortDirection.DESC ? "DESC" : "ASC";
                    orderClauses.add(orderBy.getField() + " " + direction);
                }
            }
            sql.append(String.join(", ", orderClauses));
        }
        
        // 处理LIMIT
        if (intent.getLimit() != null && intent.getLimit() > 0) {
            sql.append(" LIMIT ?");
            params.add(Math.min(intent.getLimit(), 1000)); // 最大限制1000条
        }
        
        // 处理OFFSET
        if (intent.getOffset() != null && intent.getOffset() > 0) {
            sql.append(" OFFSET ?");
            params.add(intent.getOffset());
        }
        
        return new SqlWithParams(sql.toString(), params);
    }

    /**
     * 生成INSERT语句
     */
    public SqlWithParams generateInsert(IntentDto.IntentResult intent) {
        validateTableName(intent.getTableName());
        
        if (intent.getInsertData() == null || intent.getInsertData().isEmpty()) {
            throw new IllegalArgumentException("插入数据不能为空");
        }
        
        StringBuilder sql = new StringBuilder("INSERT INTO ").append(intent.getTableName());
        List<Object> params = new ArrayList<>();
        
        // 验证字段名并过滤空值和无效值
        Map<String, Object> validData = intent.getInsertData().entrySet().stream()
                .filter(entry -> entry.getKey() != null && entry.getValue() != null)
                .filter(entry -> !isEmptyOrInvalidValue(entry.getValue()))
                .filter(entry -> isValidField(intent.getTableName(), entry.getKey()))
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
        
        if (validData.isEmpty()) {
            throw new IllegalArgumentException("没有有效的插入字段");
        }
        
        List<String> fields = new ArrayList<>(validData.keySet());
        List<String> placeholders = fields.stream()
                .map(f -> "?")
                .collect(Collectors.toList());
        
        sql.append(" (").append(String.join(", ", fields)).append(")");
        sql.append(" VALUES (").append(String.join(", ", placeholders)).append(")");
        
        // 添加参数值
        for (String field : fields) {
            params.add(validData.get(field));
        }
        
        return new SqlWithParams(sql.toString(), params);
    }

    /**
     * 生成UPDATE语句
     */
    public SqlWithParams generateUpdate(IntentDto.IntentResult intent) {
        validateTableName(intent.getTableName());
        
        if (intent.getUpdateData() == null || intent.getUpdateData().isEmpty()) {
            throw new IllegalArgumentException("更新数据不能为空");
        }
        
        StringBuilder sql = new StringBuilder("UPDATE ").append(intent.getTableName()).append(" SET ");
        List<Object> params = new ArrayList<>();
        
        // 验证更新字段
        Map<String, Object> validData = intent.getUpdateData().entrySet().stream()
                .filter(entry -> isValidField(intent.getTableName(), entry.getKey()))
                .filter(entry -> !entry.getKey().equals("id")) // 不允许更新ID
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
        
        if (validData.isEmpty()) {
            throw new IllegalArgumentException("没有有效的更新字段");
        }
        
        List<String> setClauses = new ArrayList<>();
        for (Map.Entry<String, Object> entry : validData.entrySet()) {
            setClauses.add(entry.getKey() + " = ?");
            params.add(entry.getValue());
        }
        
        sql.append(String.join(", ", setClauses));
        
        // 处理WHERE条件（UPDATE必须有条件）
        if (intent.getConditions() == null || intent.getConditions().isEmpty()) {
            throw new IllegalArgumentException("UPDATE操作必须指定WHERE条件");
        }
        
        sql.append(" WHERE ");
        appendConditions(sql, params, intent.getConditions(), intent.getTableName());
        
        return new SqlWithParams(sql.toString(), params);
    }

    /**
     * 生成DELETE语句
     */
    public SqlWithParams generateDelete(IntentDto.IntentResult intent) {
        validateTableName(intent.getTableName());
        
        StringBuilder sql = new StringBuilder("DELETE FROM ").append(intent.getTableName());
        List<Object> params = new ArrayList<>();
        
        // DELETE必须有WHERE条件
        if (intent.getConditions() == null || intent.getConditions().isEmpty()) {
            throw new IllegalArgumentException("DELETE操作必须指定WHERE条件");
        }
        
        sql.append(" WHERE ");
        appendConditions(sql, params, intent.getConditions(), intent.getTableName());
        
        return new SqlWithParams(sql.toString(), params);
    }

    /**
     * 生成COUNT查询
     */
    public SqlWithParams generateCount(IntentDto.IntentResult intent) {
        validateTableName(intent.getTableName());
        
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) as count FROM ").append(intent.getTableName());
        List<Object> params = new ArrayList<>();
        
        // 处理WHERE条件
        if (intent.getConditions() != null && !intent.getConditions().isEmpty()) {
            sql.append(" WHERE ");
            appendConditions(sql, params, intent.getConditions(), intent.getTableName());
        }
        
        return new SqlWithParams(sql.toString(), params);
    }

    /**
     * 添加WHERE条件
     */
    private void appendConditions(StringBuilder sql, List<Object> params, 
                                  List<IntentDto.Condition> conditions, String tableName) {
        for (int i = 0; i < conditions.size(); i++) {
            IntentDto.Condition condition = conditions.get(i);
            
            if (!isValidField(tableName, condition.getField())) {
                continue; // 跳过无效字段
            }
            
            if (i > 0) {
                IntentDto.LogicalOperator logical = condition.getLogical();
                sql.append(logical == IntentDto.LogicalOperator.OR ? " OR " : " AND ");
            }
            
            sql.append(condition.getField()).append(" ");
            
            switch (condition.getOperator()) {
                case EQUALS:
                    sql.append("= ?");
                    params.add(condition.getValue());
                    break;
                case NOT_EQUALS:
                    sql.append("!= ?");
                    params.add(condition.getValue());
                    break;
                case GREATER_THAN:
                    sql.append("> ?");
                    params.add(condition.getValue());
                    break;
                case LESS_THAN:
                    sql.append("< ?");
                    params.add(condition.getValue());
                    break;
                case GREATER_EQUAL:
                    sql.append(">= ?");
                    params.add(condition.getValue());
                    break;
                case LESS_EQUAL:
                    sql.append("<= ?");
                    params.add(condition.getValue());
                    break;
                case LIKE:
                    sql.append("LIKE ?");
                    params.add("%" + condition.getValue() + "%");
                    break;
                case NOT_LIKE:
                    sql.append("NOT LIKE ?");
                    params.add("%" + condition.getValue() + "%");
                    break;
                case IS_NULL:
                    sql.append("IS NULL");
                    break;
                case IS_NOT_NULL:
                    sql.append("IS NOT NULL");
                    break;
                default:
                    sql.append("= ?");
                    params.add(condition.getValue());
            }
        }
    }

    /**
     * 验证表名
     */
    private void validateTableName(String tableName) {
        if (StringUtils.isBlank(tableName) || !ALLOWED_TABLES.contains(tableName)) {
            throw new IllegalArgumentException("无效的表名: " + tableName);
        }
    }

    /**
     * 验证字段名
     */
    private boolean isValidField(String tableName, String fieldName) {
        if (StringUtils.isBlank(fieldName) || StringUtils.isBlank(tableName)) {
            return false;
        }
        
        Set<String> allowedFields = ALLOWED_FIELDS.get(tableName);
        return allowedFields != null && allowedFields.contains(fieldName);
    }

    /**
     * 检查值是否为空或无效
     */
    private boolean isEmptyOrInvalidValue(Object value) {
        if (value == null) {
            return true;
        }
        
        String stringValue = value.toString().trim();
        
        // 过滤空字符串
        if (stringValue.isEmpty()) {
            return true;
        }
        
        // 过滤特殊值
        if ("CURRENT_TIMESTAMP".equals(stringValue) || 
            "NOW()".equals(stringValue) ||
            "null".equalsIgnoreCase(stringValue)) {
            return true;
        }
        
        return false;
    }

    /**
     * 验证并过滤字段列表
     */
    private List<String> validateAndFilterFields(String tableName, List<String> fields) {
        return fields.stream()
                .filter(field -> isValidField(tableName, field))
                .collect(Collectors.toList());
    }

    /**
     * SQL和参数的组合类
     */
    public static class SqlWithParams {
        private final String sql;
        private final List<Object> params;

        public SqlWithParams(String sql, List<Object> params) {
            this.sql = sql;
            this.params = params;
        }

        public String getSql() {
            return sql;
        }

        public List<Object> getParams() {
            return params;
        }

        public Object[] getParamsArray() {
            return params.toArray();
        }
    }
}